/*
  project=projnumber
*/

* assign library aliases;
libname tax69 'path1';
libname tax74 'path2';
libname tax84 'path4';
libname tax89 'path5';
libname tax94 'path6';
libname tax99 'path7';
libname cps73 'path8';
libname cps85 'path9';
libname cps91 'path10';
libname cps95 'path11';
libname cps00 'path12';
libname xwalk 'YOUR_CROSSWALK_FILEPATH';



/* Create narrow tax files 

1969*/
DATA work.i1040_69 (KEEP = YEAR var20 var26 var4 PIKNO OVER65);
SET tax69.file1;
YEAR = 1969;
var26 = var1;
PIKNO = 1;
IF var21 = value4 THEN OVER65 = 1;
ELSE IF var21 = value3 THEN OVER65 = 0;
ELSE OVER65 = .;
RUN; 


/*1974*/
DATA work.i1040_74a (KEEP = YEAR var20 var26 var4 PIKNO OVER65);
SET tax74.file2;
YEAR = 1974;
var26 = var2;
PIKNO = 1;
IF var21 = value4 THEN OVER65 = 1;
ELSE IF var21 = value3 THEN OVER65 = 0;
ELSE OVER65 = .;
RUN;

DATA work.i1040_74b (KEEP = YEAR var20 var26 var4 PIKNO OVER65);
SET tax74.file2;
YEAR = 1974;
var26 = var3;
PIKNO = 2;
IF var21 = value4 THEN OVER65 = 1;
ELSE IF var21 = value3 THEN OVER65 = 0;
ELSE OVER65 = .;
RUN;

DATA work.i1040_74;
SET work.i1040_74a work.i1040_74b;
RUN;

* Delete working datasets to free up memory;
PROC DATASETS library=work;
DELETE i1040_74a i1040_74b;
RUN;

/*1984*/
DATA work.i1040_84a (KEEP = YEAR var20 var26 var4 PIKNO OVER65);
SET tax84.file4;
YEAR = 1984;
var26 = var5;
PIKNO = 1;
IF var23 = value4 THEN OVER65 = 1;
ELSE IF var23 = value3 THEN OVER65 = 0;
ELSE OVER65 = .;
RUN;

DATA work.i1040_84b (KEEP = YEAR var20 var26 var4 PIKNO OVER65);
SET tax84.file4;
YEAR = 1984;
var26 = var6;
PIKNO = 2;
IF var22 = value4 THEN OVER65 = 1;
ELSE IF var22 = value3 THEN OVER65 = 0;
ELSE OVER65 = .;
RUN;

DATA work.i1040_84;
SET work.i1040_84a work.i1040_84b;
RUN;

* Delete working datasets to free up memory;
PROC DATASETS library=work;
DELETE i1040_84a i1040_84b;
RUN;

/*1989*/
DATA work.i1040_89a (KEEP = YEAR var20 var26 var4 PIKNO OVER65);
SET tax89.file5;
YEAR = 1989;
var26 = var5;
PIKNO = 1;
var4 = var7;
IF var24 IN (value1, value2) THEN OVER65 = 1;
ELSE IF var24 IN (value3, value4, value5) THEN OVER65 = 0;
ELSE OVER65 = .;
RUN;

DATA work.i1040_89b (KEEP = YEAR var20 var26 var4 PIKNO OVER65);
SET tax89.file5;
YEAR = 1989;
var26 = var6;
PIKNO = 2;
var4 = var7;
IF var25 IN (value1, value2) THEN OVER65 = 1;
ELSE IF var25 IN (value3, value4, value5) THEN OVER65 = 0;
ELSE OVER65 = .;
RUN;

DATA work.i1040_89;
SET work.i1040_89a work.i1040_89b;
RUN;

* Delete working datasets to free up memory;
PROC DATASETS library=work;
DELETE i1040_89a i1040_89b;
RUN;


/*1994*/
DATA work.i1040_94a (KEEP = YEAR var20 var26 var4 PIKNO OVER65);
SET tax94.file6;
YEAR = 1994;
var26 = var5;
PIKNO = 1;
var4 = var7;
IF var24 IN (value1, value2) THEN OVER65 = 1;
ELSE IF var24 IN (value3, value4, value5) THEN OVER65 = 0;
ELSE OVER65 = .;
RUN;

DATA work.i1040_94b (KEEP = YEAR var20 var26 var4 PIKNO OVER65);
SET tax94.file6;
YEAR = 1994;
var26 = var6;
PIKNO = 2;
var4 = var7;
IF var25 IN (value1, value2) THEN OVER65 = 1;
ELSE IF var25 IN (value3, value4, value5) THEN OVER65 = 0;
ELSE OVER65 = .;
RUN;

DATA work.i1040_94;
SET work.i1040_94a work.i1040_94b;
RUN;

* Delete working datasets to free up memory;
PROC DATASETS library=work;
DELETE i1040_94a i1040_94b;
RUN;


/*1999*/
DATA work.i1040_99a (KEEP = YEAR var20 var26 var4 PIKNO OVER65);
SET tax99.file7;
YEAR = 1999;
var26 = var5;
PIKNO = 1;
var4 = var7;
IF var24 IN (value1, value2) THEN OVER65 = 1;
ELSE IF var24 IN (value3, value4, value5) THEN OVER65 = 0;
ELSE OVER65 = .;
RUN;

DATA work.i1040_99b (KEEP = YEAR var20 var26 var4 PIKNO OVER65);
SET tax99.file7;
YEAR = 1999;
var26 = var6;
PIKNO = 2;
var4 = var7;
IF var25 IN (value1, value2) THEN OVER65 = 1;
ELSE IF var25 IN (value3, value4, value5) THEN OVER65 = 0;
ELSE OVER65 = .;
RUN;

DATA work.i1040_99;
SET work.i1040_99a work.i1040_99b;
RUN;

* Delete working datasets to free up memory;
PROC DATASETS library=work;
DELETE i1040_99a i1040_99b;
RUN;

/*--------- CPS ----------------------*/

/*Call in the crosswalks*/
DATA work.xwalk73 (KEEP = var26 var27 var28);
SET xwalk.file8;
WHERE (var27 IS NOT MISSING) AND (var28 NE '');
RUN;

PROC SORT DATA=work.xwalk73;
  BY var27 var28;
RUN;

DATA work.xwalk85 (KEEP = var26 var29 var30);
SET xwalk.file8;
WHERE (var29 IS NOT MISSING) AND (var30 NE '');
RUN;

PROC SORT DATA=work.xwalk85;
  BY var29 var30;
RUN;

DATA work.xwalk91 (KEEP = var26 var31 var32);
SET xwalk.file8;
WHERE (var31 IS NOT MISSING) AND (var32 NE '');
RUN;

PROC SORT DATA=work.xwalk91;
  BY var31 var32;
RUN;

DATA work.xwalk95 (KEEP = var26 var33 var34);
SET xwalk.file8;
WHERE (var33 IS NOT MISSING) AND (var34 NE '');
RUN;

PROC SORT DATA=work.xwalk95;
  BY var33 var34;
RUN;

DATA work.xwalk00 (KEEP = var26 var35 var36);
SET xwalk.file8;
WHERE (var35 IS NOT MISSING) AND (var36 NE '');
RUN;

PROC SORT DATA=work.xwalk00;
  BY var35 var36;
RUN;

/*Merge crosswalks with CPS files*/
PROC SQL;

/*Create sub-set of married couples in 1973 CPS*/

CREATE TABLE work.cps73 AS
SELECT * FROM (

  SELECT 
  a.var37, var38, var39, var40, var41, var42, var43, var44
  FROM cps73.file9 a
  LEFT JOIN (
    SELECT
    var37,
    MAX(CASE WHEN INPUT(var39, 3.)>=value10 THEN 1 ELSE 0 END) AS var43,
    MAX(CASE WHEN INPUT(var39, 3.)>=value11 THEN 1 ELSE 0 END) AS var44
    FROM cps73.file9
    WHERE var41 = value1 AND var42 in ("value13", "value14") AND INPUT(var39, 3.)>=value12
    GROUP BY var37
  ) b
  ON a.var37=b.var37
  WHERE var41 = value1 AND var42 in ("value13", "value14") AND INPUT(var39, 3.)>=value12

  UNION ALL

  SELECT 
  var37, var38, var39, var40, var41, var42, . AS var43, . AS var44
  FROM cps73.file9 
  WHERE (var41 NE value1 OR var42 NOT IN ("value13", "value14")) AND INPUT(var39, 3.)>=value12
) temp
ORDER BY var37, var38
;
QUIT;

DATA work.cps73_pik_raw;
MERGE work.cps73(IN=a) work.xwalk73(IN=b RENAME=(var27=var37 var28=var38));
BY var37 var38;
IF a and b;
RUN;

PROC SORT DATA=cps85.file10 OUT=work.cps85 (KEEP=var37 var38 var39 var40 var41);
BY var37 var38;
WHERE INPUT(var39, 3.)>=value12;
RUN;

DATA work.cps85_pik_raw;
MERGE work.cps85(IN=a) work.xwalk85(IN=b RENAME=(var29=var37 var30=var38));
BY var37 var38;
IF a and b;
RUN;

PROC SORT DATA=cps91.file11 OUT=work.cps91 (KEEP=var37 var38 var39 var40 var41);
BY var37 var38;
WHERE INPUT(var39, 3.)>=value12;
RUN;

DATA work.cps91_pik_raw;
MERGE work.cps91(IN=a) work.xwalk91(IN=b RENAME=(var31=var37 var32=var38));
BY var37 var38;
IF a and b;
RUN;

PROC SORT DATA=cps95.file12 OUT=work.cps95 (KEEP=var37 var38 var39 var40 var41);
BY var37 var38;
WHERE INPUT(var39, 3.)>=value12;
RUN;

DATA work.cps95_pik_raw;
MERGE work.cps95(IN=a) work.xwalk95(IN=b RENAME=(var33=var37 var34=var38));
BY var37 var38;
IF a and b;
RUN;

PROC SORT DATA=cps00.file13 OUT=work.cps00 (KEEP=var37 var38 var39 var40 var41);
BY var37 var38;
WHERE INPUT(var39, 3.)>=value12;
RUN;

DATA work.cps00_pik_raw;
MERGE work.cps00(IN=a) work.xwalk00(IN=b RENAME=(var35=var37 var36=var38));
BY var37 var38;
IF a and b;
RUN;

/*Delete all records with duplicate PIKs*/
PROC SQL;
CREATE TABLE work.cps73_pik AS
SELECT *
FROM work.cps73_pik_raw
GROUP BY var26
HAVING COUNT(*)=1
;

CREATE TABLE work.cps85_pik AS
SELECT *
FROM work.cps85_pik_raw
GROUP BY var26
HAVING COUNT(*)=1
;

CREATE TABLE work.cps91_pik AS
SELECT *
FROM work.cps91_pik_raw
GROUP BY var26
HAVING COUNT(*)=1
;

CREATE TABLE work.cps95_pik AS
SELECT *
FROM work.cps95_pik_raw
GROUP BY var26
HAVING COUNT(*)=1
;

CREATE TABLE work.cps00_pik AS
SELECT *
FROM work.cps00_pik_raw
GROUP BY var26
HAVING COUNT(*)=1
;
QUIT;

/* Output the first column of table summarizing number of CPS records with PIKs assigned*/
TITLE "Table 10, Part 1";

PROC SQL;
SELECT "1973" AS YEAR, COUNT(*) AS TOTAL_PIKS FROM work.cps73_pik
UNION ALL
SELECT "1973 - Male" AS YEAR, COUNT(*) AS TOTAL_PIKS FROM work.cps73_pik WHERE var40 = 'value4'
UNION ALL
SELECT "1973 - Female" AS YEAR, COUNT(*) AS TOTAL_PIKS FROM work.cps73_pik WHERE var40 = 'value1'
UNION ALL
SELECT "1985" AS YEAR, COUNT(*) AS TOTAL_PIKS FROM work.cps85_pik
UNION ALL
SELECT "1991" AS YEAR, COUNT(*) AS TOTAL_PIKS FROM work.cps91_pik
UNION ALL
SELECT "1995" AS YEAR, COUNT(*) AS TOTAL_PIKS FROM work.cps95_pik
UNION ALL
SELECT "2000" AS YEAR, COUNT(*) AS TOTAL_PIKS FROM work.cps00_pik
;
QUIT;

/*Merge CPS with tax files */
PROC SORT DATA=work.cps73_pik;
BY var26;
RUN;

PROC SORT DATA=work.i1040_69;
BY var26;
RUN;

DATA work.cps73_irs69_raw;
MERGE work.cps73_pik(IN=a) work.i1040_69;
BY var26;
IF a;
RUN;

PROC SORT DATA=work.i1040_74;
BY var26;
RUN;

DATA work.cps73_irs74_raw;
MERGE work.cps73_pik(IN=a) work.i1040_74;
BY var26;
IF a;
RUN;

PROC SORT DATA=work.cps85_pik;
BY var26;
RUN;

PROC SORT DATA=work.i1040_84;
BY var26;
RUN;

DATA work.cps85_irs84_raw;
MERGE work.cps85_pik(IN=a) work.i1040_84;
BY var26;
IF a;
RUN;

PROC SORT DATA=work.cps91_pik;
BY var26;
RUN;

PROC SORT DATA=work.i1040_89;
BY var26;
RUN;

DATA work.cps91_irs89_raw;
MERGE work.cps91_pik(IN=a) work.i1040_89;
BY var26;
IF a;
RUN;

PROC SORT DATA=work.cps95_pik;
BY var26;
RUN;

PROC SORT DATA=work.i1040_94;
BY var26;
RUN;

DATA work.cps95_irs94_raw;
MERGE work.cps95_pik(IN=a) work.i1040_94;
BY var26;
IF a;
RUN;

PROC SORT DATA=work.cps00_pik;
BY var26;
RUN;

PROC SORT DATA=work.i1040_99;
BY var26;
RUN;

DATA work.cps00_irs99_raw;
MERGE work.cps00_pik(IN=a) work.i1040_99;
BY var26;
IF a;
RUN;

/* Output for data supporting Table 10, to calculate how many duplicate records were removed*/

TITLE "Table 10, supporting data";

PROC SQL;
SELECT "1973 CPS TO 1969 TAX" AS LABEL, COUNT(*) AS TOTAL_DUPLICATE_PIKS FROM 
  (SELECT var26 FROM work.cps73_irs69_raw WHERE var20 IS NOT NULL GROUP BY var26 HAVING COUNT(*)>1)
UNION ALL
SELECT "1973 CPS TO 1969 TAX - MALE" AS LABEL, COUNT(*) AS TOTAL_DUPLICATE_PIKS FROM 
  (SELECT var26 FROM work.cps73_irs69_raw WHERE var20 IS NOT NULL AND var40 = 'value4' GROUP BY var26 HAVING COUNT(*)>1)
UNION ALL
SELECT "1973 CPS TO 1969 TAX - FEMALE" AS LABEL, COUNT(*) AS TOTAL_DUPLICATE_PIKS FROM 
  (SELECT var26 FROM work.cps73_irs69_raw WHERE var20 IS NOT NULL AND var40 = 'value1' GROUP BY var26 HAVING COUNT(*)>1)
UNION ALL
SELECT "1973 CPS TO 1974 TAX" AS LABEL, COUNT(*) AS TOTAL_DUPLICATE_PIKS FROM 
  (SELECT var26 FROM work.cps73_irs74_raw WHERE var20 IS NOT NULL GROUP BY var26 HAVING COUNT(*)>1)
UNION ALL
SELECT "1985 CPS TO 1984 TAX" AS LABEL, COUNT(*) AS TOTAL_DUPLICATE_PIKS FROM 
  (SELECT var26 FROM work.cps85_irs84_raw WHERE var20 IS NOT NULL GROUP BY var26 HAVING COUNT(*)>1)
UNION ALL
SELECT "1991 CPS TO 1989 TAX" AS LABEL, COUNT(*) AS TOTAL_DUPLICATE_PIKS FROM 
  (SELECT var26 FROM work.cps91_irs89_raw WHERE var20 IS NOT NULL GROUP BY var26 HAVING COUNT(*)>1)
UNION ALL
SELECT "1995 CPS TO 1994 TAX" AS LABEL, COUNT(*) AS TOTAL_DUPLICATE_PIKS FROM 
  (SELECT var26 FROM work.cps95_irs94_raw WHERE var20 IS NOT NULL GROUP BY var26 HAVING COUNT(*)>1)
UNION ALL
SELECT "2000 CPS TO 1999 TAX" AS LABEL, COUNT(*) AS TOTAL_DUPLICATE_PIKS FROM 
  (SELECT var26 FROM work.cps00_irs99_raw WHERE var20 IS NOT NULL GROUP BY var26 HAVING COUNT(*)>1)
;
QUIT;


/*--------------Removing duplicates----------*/
PROC SQL;
CREATE TABLE work.cps73_irs69 AS
SELECT *
FROM work.cps73_irs69_raw
GROUP BY var26
HAVING COUNT(*)=1
;

PROC SQL;
CREATE TABLE work.cps73_irs74 AS
SELECT *
FROM work.cps73_irs74_raw
GROUP BY var26
HAVING COUNT(*)=1
;

PROC SQL;
CREATE TABLE work.cps85_irs84 AS
SELECT *
FROM work.cps85_irs84_raw
GROUP BY var26
HAVING COUNT(*)=1
;

PROC SQL;
CREATE TABLE work.cps91_irs89 AS
SELECT *
FROM work.cps91_irs89_raw
GROUP BY var26
HAVING COUNT(*)=1
;

PROC SQL;
CREATE TABLE work.cps95_irs94 AS
SELECT *
FROM work.cps95_irs94_raw
GROUP BY var26
HAVING COUNT(*)=1
;

PROC SQL;
CREATE TABLE work.cps00_irs99 AS
SELECT *
FROM work.cps00_irs99_raw
GROUP BY var26
HAVING COUNT(*)=1
;
QUIT;


/*------------------Creating ouput--------------------*/

/*Table 10, Second column showing number of records linked between files*/

TITLE "Table 10, Column 2";

PROC SQL;
SELECT "1973 CPS TO 1969 TAX" AS LABEL, COUNT(*) AS TOTAL_LINKED FROM work.cps73_irs69 WHERE var20 IS NOT NULL
UNION ALL
SELECT "1973 CPS TO 1969 TAX - MALE" AS LABEL, COUNT(*) AS TOTAL_LINKED FROM work.cps73_irs69 WHERE var20 IS NOT NULL AND var40 = 'value4'
UNION ALL
SELECT "1973 CPS TO 1969 TAX - FEMALE" AS LABEL, COUNT(*) AS TOTAL_LINKED FROM work.cps73_irs69 WHERE var20 IS NOT NULL AND var40 = 'value1'
UNION ALL
SELECT "1973 CPS TO 1974 TAX" AS LABEL, COUNT(*) AS TOTAL_LINKED FROM work.cps73_irs74 WHERE var20 IS NOT NULL
UNION ALL
SELECT "1985 CPS TO 1984 TAX" AS LABEL, COUNT(*) AS TOTAL_LINKED FROM work.cps85_irs84 WHERE var20 IS NOT NULL
UNION ALL
SELECT "1991 CPS TO 1989 TAX" AS LABEL, COUNT(*) AS TOTAL_LINKED FROM work.cps91_irs89 WHERE var20 IS NOT NULL
UNION ALL
SELECT "1995 CPS TO 1994 TAX" AS LABEL, COUNT(*) AS TOTAL_LINKED FROM work.cps95_irs94 WHERE var20 IS NOT NULL
UNION ALL
SELECT "2000 CPS TO 1999 TAX" AS LABEL, COUNT(*) AS TOTAL_LINKED FROM work.cps00_irs99 WHERE var20 IS NOT NULL
;
QUIT;

/*Table 11*/

TITLE "Table 11";

PROC SQL;
SELECT
"1973 CPS TO 1969 TAX" AS LABEL,
COUNT(*) AS TOTAL_PIKS,
SUM(CASE WHEN var41 IN (value1, value2, value5, value6) AND var4 IN (value1, value2, value7) THEN 1 ELSE 0 END) AS Both_Married,
SUM(CASE WHEN var41 IN (value3, value4, value7, value8, value9) AND var4 IN (value4, value5, value6) THEN 1 ELSE 0 END) AS Both_Unmarried,
SUM(CASE WHEN (var41 IN (value3, value4, value7, value8, value9) AND var4 IN (value1, value2, value7))
          OR (var41 IN (value1, value2, value5, value6) AND var4 IN (value4, value5, value6)) THEN 1 ELSE 0 END) AS Marital_Status_Unmatched,
SUM(CASE WHEN var41 IS NULL OR var4 IS NULL THEN 1 ELSE 0 END) AS Marital_Status_Blank
FROM work.cps73_irs69
WHERE var20 IS NOT NULL

UNION ALL

SELECT
"1973 CPS TO 1974 TAX" AS LABEL,
COUNT(*) AS TOTAL_PIKS,
SUM(CASE WHEN var41 IN (value1, value2, value5, value6) AND var4 IN (value1, value2, value7) THEN 1 ELSE 0 END) AS Both_Married,
SUM(CASE WHEN var41 IN (value3, value4, value7, value8, value9) AND var4 IN (value4, value5, value6) THEN 1 ELSE 0 END) AS Both_Unmarried,
SUM(CASE WHEN (var41 IN (value3, value4, value7, value8, value9) AND var4 IN (value1, value2, value7))
          OR (var41 IN (value1, value2, value5, value6) AND var4 IN (value4, value5, value6)) THEN 1 ELSE 0 END) AS Marital_Status_Unmatched,
SUM(CASE WHEN var41 IS NULL OR var4 IS NULL THEN 1 ELSE 0 END) AS Marital_Status_Blank
FROM work.cps73_irs74
WHERE var20 IS NOT NULL

UNION ALL

SELECT
"1985 CPS TO 1984 TAX" AS LABEL,
COUNT(*) AS TOTAL_PIKS,
SUM(CASE WHEN var41 IN (value4, value1, value2, value5, value8) AND var4 IN (value1, value2, value7) THEN 1 ELSE 0 END) AS Both_Married,
SUM(CASE WHEN var41 IN (value6, value7, value9) AND var4 IN (value4, value5, value6) THEN 1 ELSE 0 END) AS Both_Unmarried,
SUM(CASE WHEN (var41 IN (value4, value1, value2, value5, value8) AND var4 IN (value4, value5, value6))
          OR (var41 IN (value6, value7, value9) AND var4 IN (value1, value2, value7)) THEN 1 ELSE 0 END) AS Marital_Status_Unmatched,
SUM(CASE WHEN var41 IS NULL OR var4 IS NULL THEN 1 ELSE 0 END) AS Marital_Status_Blank
FROM work.cps85_irs84
WHERE var20 IS NOT NULL

UNION ALL

SELECT
"1991 CPS TO 1989 TAX" AS LABEL,
COUNT(*) AS TOTAL_PIKS,
SUM(CASE WHEN var41 IN (value4, value1, value2, value7) AND var4 IN (value1, value2, value7) THEN 1 ELSE 0 END) AS Both_Married,
SUM(CASE WHEN var41 IN (value6, value8, value5) AND var4 IN (value4, value5, value6) THEN 1 ELSE 0 END) AS Both_Unmarried,
SUM(CASE WHEN (var41 IN (value4, value1, value2, value7) AND var4 IN (value4, value5, value6))
          OR (var41 IN (value6, value8, value5) AND var4 IN (value1, value2, value7)) THEN 1 ELSE 0 END) AS Marital_Status_Unmatched,
SUM(CASE WHEN var41 IS NULL OR var4 IS NULL THEN 1 ELSE 0 END) AS Marital_Status_Blank
FROM work.cps91_irs89
WHERE var20 IS NOT NULL

UNION ALL

SELECT
"1995 CPS TO 1994 TAX" AS LABEL,
COUNT(*) AS TOTAL_PIKS,
SUM(CASE WHEN var41 IN (value4, value1, value2, value7) AND var4 IN (value1, value2, value7) THEN 1 ELSE 0 END) AS Both_Married,
SUM(CASE WHEN var41 IN (value6, value8, value5) AND var4 IN (value4, value5, value6) THEN 1 ELSE 0 END) AS Both_Unmarried,
SUM(CASE WHEN (var41 IN (value4, value1, value2, value7) AND var4 IN (value4, value5, value6))
          OR (var41 IN (value6, value8, value5) AND var4 IN (value1, value2, value7)) THEN 1 ELSE 0 END) AS Marital_Status_Unmatched,
SUM(CASE WHEN var41 IS NULL OR var4 IS NULL THEN 1 ELSE 0 END) AS Marital_Status_Blank
FROM work.cps95_irs94
WHERE var20 IS NOT NULL

UNION ALL

SELECT
"2000 CPS TO 1999 TAX" AS LABEL,
COUNT(*) AS TOTAL_PIKS,
SUM(CASE WHEN var41 IN (value4, value1, value2, value7) AND var4 IN (value1, value2, value7) THEN 1 ELSE 0 END) AS Both_Married,
SUM(CASE WHEN var41 IN (value6, value8, value5) AND var4 IN (value3, value4, value5, value6, value8) THEN 1 ELSE 0 END) AS Both_Unmarried,
SUM(CASE WHEN (var41 IN (value4, value1, value2, value7) AND var4 IN (value3, value4, value5, value6, value8))
          OR (var41 IN (value6, value8, value5) AND var4 IN (value1, value2, value7)) THEN 1 ELSE 0 END) AS Marital_Status_Unmatched,
SUM(CASE WHEN var41 IS NULL OR var4 IS NULL THEN 1 ELSE 0 END) AS Marital_Status_Blank
FROM work.cps00_irs99
WHERE var20 IS NOT NULL
;
QUIT;

/*Table 12

This uses a different approach across years. 
1969 and 1974 have only a return-level flag for either filer or spouse being 65+ or blind.
var4 value6 should be omitted because the deceased individual is included in the 65+/blind flag.
The remaining years have an individual-level variables for primary and secondary filers' 65+ exemption.*/

TITLE "Table 12";

PROC SQL;
SELECT
"1973 CPS TO 1969 TAX" AS LABEL,
COUNT(*) AS TOTAL_PIKS,
SUM(CASE WHEN (var4 = value1 AND OVER65=1 AND var43=value4) OR 
              (var4 IN (value4, value2, value5, value7) AND OVER65=1 AND INPUT(var39, 3.)>=value10) THEN 1 ELSE 0 END) AS Age65over_both,
SUM(CASE WHEN (var4 = value1 AND OVER65=0 AND var43=value3) OR
              (var4 IN (value4, value2, value5, value7) AND OVER65=0 AND INPUT(var39, 3.)<value10) THEN 1 ELSE 0 END) AS AgeUnder65_both,
SUM(CASE WHEN (var4 = value1 AND ((OVER65=1 AND var43=value3) OR (OVER65=0 AND var43=value4))) OR
              (var4 IN (value4, value2, value5, value7) AND ((OVER65=1 AND INPUT(var39, 3.)<value10) OR (OVER65=0 AND INPUT(var39, 3.)>=value10))) THEN 1 ELSE 0 END) AS Age_mismatch,
SUM(CASE WHEN (var4 = value1 AND (OVER65 IS MISSING OR var43 IS MISSING)) OR
              (var4 IN (value4, value2, value5, value7) AND (OVER65 IS MISSING OR var39 IS MISSING)) THEN 1 ELSE 0 END) AS Age_missing
FROM work.cps73_irs69
WHERE var20 IS NOT NULL AND var4 IN (value4, value1, value2, value5, value7)

UNION ALL

SELECT
"1973 CPS TO 1974 TAX" AS LABEL,
COUNT(*) AS TOTAL_PIKS,
SUM(CASE WHEN (var4 = value1 AND OVER65=1 AND var44=value4) OR 
              (var4 IN (value4, value2, value5, value7) AND OVER65=1 AND INPUT(var39, 3.)>=value11) THEN 1 ELSE 0 END) AS Age65over_both,
SUM(CASE WHEN (var4 = value1 AND OVER65=0 AND var44=value3) OR
              (var4 IN (value4, value2, value5, value7) AND OVER65=0 AND INPUT(var39, 3.)<value11) THEN 1 ELSE 0 END) AS AgeUnder65_both,
SUM(CASE WHEN (var4 = value1 AND ((OVER65=1 AND var44=value3) OR (OVER65=0 AND var44=value4))) OR
              (var4 IN (value4, value2, value5, value7) AND ((OVER65=1 AND INPUT(var39, 3.)<value11) OR (OVER65=0 AND INPUT(var39, 3.)>=value11))) THEN 1 ELSE 0 END) AS Age_mismatch,
SUM(CASE WHEN (var4 = value1 AND (OVER65 IS MISSING OR var44 IS MISSING)) OR
              (var4 IN (value4, value2, value5, value7) AND (OVER65 IS MISSING OR var39 IS MISSING)) THEN 1 ELSE 0 END) AS Age_missing
FROM work.cps73_irs74
WHERE var20 IS NOT NULL AND var4 IN (value4, value1, value2, value5, value7)

UNION ALL

SELECT
"1985 CPS TO 1984 TAX" AS LABEL,
COUNT(*) AS TOTAL_PIKS,
SUM(CASE WHEN OVER65=1 AND INPUT(var39, 3.)>=value15 THEN 1 ELSE 0 END) AS Age65over_both,
SUM(CASE WHEN OVER65=0 AND INPUT(var39, 3.)<value15 THEN 1 ELSE 0 END) AS AgeUnder65_both,
SUM(CASE WHEN (OVER65=1 AND INPUT(var39, 3.)<value15) OR (OVER65=0 AND INPUT(var39, 3.)>=value15) THEN 1 ELSE 0 END) AS Age_mismatch,
SUM(CASE WHEN OVER65 IS MISSING OR var39='' THEN 1 ELSE 0 END) AS Age_missing
FROM work.cps85_irs84
WHERE var20 IS NOT NULL

UNION ALL

SELECT
"1991 CPS TO 1989 TAX" AS LABEL,
COUNT(*) AS TOTAL_PIKS,
SUM(CASE WHEN OVER65=1 AND INPUT(var39, 3.)>=value16 THEN 1 ELSE 0 END) AS Age65over_both,
SUM(CASE WHEN OVER65=0 AND INPUT(var39, 3.)<value16 THEN 1 ELSE 0 END) AS AgeUnder65_both,
SUM(CASE WHEN (OVER65=1 AND INPUT(var39, 3.)<value16) OR (OVER65=0 AND INPUT(var39, 3.)>=value16) THEN 1 ELSE 0 END) AS Age_mismatch,
SUM(CASE WHEN OVER65 IS MISSING OR var39='' THEN 1 ELSE 0 END) AS Age_missing
FROM work.cps91_irs89
WHERE var20 IS NOT NULL

UNION ALL

SELECT
"1995 CPS TO 1994 TAX" AS LABEL,
COUNT(*) AS TOTAL_PIKS,
SUM(CASE WHEN OVER65=1 AND INPUT(var39, 3.)>=value15 THEN 1 ELSE 0 END) AS Age65over_both,
SUM(CASE WHEN OVER65=0 AND INPUT(var39, 3.)<value15 THEN 1 ELSE 0 END) AS AgeUnder65_both,
SUM(CASE WHEN (OVER65=1 AND INPUT(var39, 3.)<value15) OR (OVER65=0 AND INPUT(var39, 3.)>=value15) THEN 1 ELSE 0 END) AS Age_mismatch,
SUM(CASE WHEN OVER65 IS MISSING OR var39='' THEN 1 ELSE 0 END) AS Age_missing
FROM work.cps95_irs94
WHERE var20 IS NOT NULL

UNION ALL

SELECT
"2000 CPS TO 1999 TAX" AS LABEL,
COUNT(*) AS TOTAL_PIKS,
SUM(CASE WHEN OVER65=1 AND INPUT(var39, 3.)>=value15 THEN 1 ELSE 0 END) AS Age65over_both,
SUM(CASE WHEN OVER65=0 AND INPUT(var39, 3.)<value15 THEN 1 ELSE 0 END) AS AgeUnder65_both,
SUM(CASE WHEN (OVER65=1 AND INPUT(var39, 3.)<value15) OR (OVER65=0 AND INPUT(var39, 3.)>=value15) THEN 1 ELSE 0 END) AS Age_mismatch,
SUM(CASE WHEN OVER65 IS MISSING OR var39='' THEN 1 ELSE 0 END) AS Age_missing
FROM work.cps00_irs99
WHERE var20 IS NOT NULL
;
QUIT;
